package CarManagement;

import CollegeInformation.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class CarDao {

    public void add(Car car){
        Connection conn=null;
        try{
           conn = DBUtil.connection();
            String sql;
            if(car.getType().equals("大客车")){
                sql="INSERT INTO Car(id,license,company,buyTime,mile,type,oilConsumption,protectMoney,roadMoney,sum,passengerNum) VALUES(?,?,?,?,?,?,?,?,?,?,?)";
                BigCar bigCar=(BigCar)car;
                PreparedStatement preparedStatement=conn.prepareStatement(sql);
                preparedStatement.setString(1,bigCar.getId());
                preparedStatement.setString(2,bigCar.getLicense());
                preparedStatement.setString(3,bigCar.getCompany());
                preparedStatement.setString(4,bigCar.getBuyTime());
                preparedStatement.setInt(5,bigCar.getMile());
                preparedStatement.setString(6,bigCar.getType());
                preparedStatement.setFloat(7,bigCar.getOilConsumption());
                preparedStatement.setInt(8,bigCar.getProtectMoney());
                preparedStatement.setInt(9,bigCar.getRoadMoney());
                preparedStatement.setFloat(10,bigCar.getSum());
                preparedStatement.setInt(11,bigCar.getPassengerNum());
                preparedStatement.execute();
            }
            if(car.getType().equals("小轿车")){
                sql="INSERT INTO Car(id,license,company,buyTime,mile,type,oilConsumption,protectMoney,roadMoney,sum,boxNum) VALUES(?,?,?,?,?,?,?,?,?,?,?)";
                SmallCar smallCar=(SmallCar) car;
                PreparedStatement preparedStatement=conn.prepareStatement(sql);
                preparedStatement.setString(1,smallCar.getId());
                preparedStatement.setString(2,smallCar.getLicense());
                preparedStatement.setString(3,smallCar.getCompany());
                preparedStatement.setString(4,smallCar.getBuyTime());
                preparedStatement.setInt(5,smallCar.getMile());
                preparedStatement.setString(6,smallCar.getType());
                preparedStatement.setFloat(7,smallCar.getOilConsumption());
                preparedStatement.setInt(8,smallCar.getProtectMoney());
                preparedStatement.setInt(9,smallCar.getRoadMoney());
                preparedStatement.setFloat(10,smallCar.getSum());
                preparedStatement.setInt(11,smallCar.getBoxNum());
                preparedStatement.execute();

            }
            if(car.getType().equals("卡车")){
                sql="INSERT INTO Car(id,license,company,buyTime,mile,type,oilConsumption,protectMoney,roadMoney,sum,Weight) VALUES(?,?,?,?,?,?,?,?,?,?,?)";
                Track track=(Track) car;
                PreparedStatement preparedStatement=conn.prepareStatement(sql);
                preparedStatement.setString(1,track.getId());
                preparedStatement.setString(2,track.getLicense());
                preparedStatement.setString(3,track.getCompany());
                preparedStatement.setString(4,track.getBuyTime());
                preparedStatement.setInt(5,track.getMile());
                preparedStatement.setString(6,track.getType());
                preparedStatement.setFloat(7,track.getOilConsumption());
                preparedStatement.setInt(8,track.getProtectMoney());
                preparedStatement.setInt(9,track.getRoadMoney());
                preparedStatement.setFloat(10,track.getSum());
                preparedStatement.setInt(11,track.getWeight());
                preparedStatement.execute();
            }
            System.out.println("添加车辆成功");
        }
        catch (Exception e){
            System.out.println("该车辆id已存在");

        }
        finally {
            if (conn!= null) {
                try {
                    conn.close();
                } catch (SQLException e) {



                }
            }
        }
    }

    public List<Car> displayall()  {
        Connection conn=null;
        Statement statement=null;
        ResultSet resultSet=null;
        String sql;
        try{
            conn=DBUtil.connection();
            List<Car> carList=new ArrayList<Car>();
            sql="SELECT * FROM Car";
            statement= conn.createStatement();
            resultSet=statement.executeQuery(sql);
            while(resultSet.next()){
                if(resultSet.getString("type").equals("大客车")){
                    BigCar bigCar=new BigCar(resultSet.getString("id"),resultSet.getString("license"),resultSet.getString("company"),resultSet.getString("buyTime"),resultSet.getString("type"),resultSet.getInt("mile"),resultSet.getFloat("oilConsumption"),resultSet.getInt("protectMoney"),resultSet.getInt("roadMoney"),resultSet.getFloat("sum"),resultSet.getInt("passengerNum"));
                    carList.add(bigCar);
                }
                if(resultSet.getString("type").equals("小轿车")){
                    SmallCar smallCar=new SmallCar(resultSet.getString("id"),resultSet.getString("license"),resultSet.getString("company"),resultSet.getString("buyTime"),resultSet.getString("type"),resultSet.getInt("mile"),resultSet.getFloat("oilConsumption"),resultSet.getInt("protectMoney"),resultSet.getInt("roadMoney"),resultSet.getFloat("sum"),resultSet.getInt("boxNum"));
                    carList.add(smallCar);
                }
                if(resultSet.getString("type").equals("卡车")){
                    Track track=new Track(resultSet.getString("id"),resultSet.getString("license"),resultSet.getString("company"),resultSet.getString("buyTime"),resultSet.getString("type"),resultSet.getInt("mile"),resultSet.getFloat("oilConsumption"),resultSet.getInt("protectMoney"),resultSet.getInt("roadMoney"),resultSet.getFloat("sum"),resultSet.getInt("Weight"));
                    carList.add(track);
                }
            }
            return carList;
        }  catch (Exception e) {
            e.printStackTrace();
            return null;
        }finally {
            if(resultSet!=null){try {
                resultSet.close();}catch (SQLException e){}
            }
            if(statement!=null){try {
                statement.close();}catch (SQLException e){}
            }
            if(conn!=null){try {
                conn.close();}catch (SQLException e){}
            }
        }

    }

    public List<Car> QueryCompany(String company){
        Connection conn=null;
        Statement statement=null;
        ResultSet resultSet=null;
        List<Car> carList=new ArrayList<Car>();
        try{
            conn=DBUtil.connection();
            String sql="SELECT * FROM Car WHERE company='"+company+"'";
            statement= conn.createStatement();
            resultSet=statement.executeQuery(sql);
            while(resultSet.next()){

                    if(resultSet.getString("type").equals("大客车")){
                        BigCar bigCar=new BigCar(resultSet.getString("id"),resultSet.getString("license"),resultSet.getString("company"),resultSet.getString("buyTime"),resultSet.getString("type"),resultSet.getInt("mile"),resultSet.getFloat("oilConsumption"),resultSet.getInt("protectMoney"),resultSet.getInt("roadMoney"),resultSet.getFloat("sum"),resultSet.getInt("passengerNum"));
                        carList.add(bigCar);
                    }
                    if(resultSet.getString("type").equals("小轿车")){
                        SmallCar smallCar=new SmallCar(resultSet.getString("id"),resultSet.getString("license"),resultSet.getString("company"),resultSet.getString("buyTime"),resultSet.getString("type"),resultSet.getInt("mile"),resultSet.getFloat("oilConsumption"),resultSet.getInt("protectMoney"),resultSet.getInt("roadMoney"),resultSet.getFloat("sum"),resultSet.getInt("boxNum"));
                        carList.add(smallCar);
                    }
                    if(resultSet.getString("type").equals("卡车")){
                        Track track=new Track(resultSet.getString("id"),resultSet.getString("license"),resultSet.getString("company"),resultSet.getString("buyTime"),resultSet.getString("type"),resultSet.getInt("mile"),resultSet.getFloat("oilConsumption"),resultSet.getInt("protectMoney"),resultSet.getInt("roadMoney"),resultSet.getFloat("sum"),resultSet.getInt("Weight"));
                        carList.add(track);
                    }



            }
            return carList;
        }catch (Exception e) {
            e.printStackTrace();
            return null;
        }finally {
            if(resultSet!=null){try {
                resultSet.close();}catch (SQLException e){}
            }
            if(statement!=null){try {
                statement.close();}catch (SQLException e){}
            }
            if(conn!=null){try {
                conn.close();}catch (SQLException e){}
            }
        }
    }

    public Car QueryId(String id){
        Connection conn=null;
        Statement statement=null;
        ResultSet resultSet=null;
        List<Car> carList=new ArrayList<Car>();
        try{
            conn=DBUtil.connection();
            String sql="SELECT * FROM Car WHERE id='"+id+"'";
            statement= conn.createStatement();
            resultSet=statement.executeQuery(sql);
            while(resultSet.next()){

                    if(resultSet.getString("type").equals("大客车")){
                        BigCar bigCar=new BigCar(resultSet.getString("id"),resultSet.getString("license"),resultSet.getString("company"),resultSet.getString("buyTime"),resultSet.getString("type"),resultSet.getInt("mile"),resultSet.getFloat("oilConsumption"),resultSet.getInt("protectMoney"),resultSet.getInt("roadMoney"),resultSet.getFloat("sum"),resultSet.getInt("passengerNum"));
                        carList.add(bigCar);
                    }
                    if(resultSet.getString("type").equals("小轿车")){
                        SmallCar smallCar=new SmallCar(resultSet.getString("id"),resultSet.getString("license"),resultSet.getString("company"),resultSet.getString("buyTime"),resultSet.getString("type"),resultSet.getInt("mile"),resultSet.getFloat("oilConsumption"),resultSet.getInt("protectMoney"),resultSet.getInt("roadMoney"),resultSet.getFloat("sum"),resultSet.getInt("boxNum"));
                        carList.add(smallCar);
                    }
                    if(resultSet.getString("type").equals("卡车")){
                        Track track=new Track(resultSet.getString("id"),resultSet.getString("license"),resultSet.getString("company"),resultSet.getString("buyTime"),resultSet.getString("type"),resultSet.getInt("mile"),resultSet.getFloat("oilConsumption"),resultSet.getInt("protectMoney"),resultSet.getInt("roadMoney"),resultSet.getFloat("sum"),resultSet.getInt("Weight"));
                        carList.add(track);
                    }

                }

            return carList.get(0);
        }catch (Exception e) {
            e.printStackTrace();
            return null;
        }finally {
            if(resultSet!=null){try {
                resultSet.close();}catch (SQLException e){}
            }
            if(statement!=null){try {
                statement.close();}catch (SQLException e){}
            }
            if(conn!=null){try {
                conn.close();}catch (SQLException e){}
            }
        }
    }

    public List<Car> QueryType(String type){
        Connection conn=null;
        Statement statement=null;
        ResultSet resultSet=null;
        List<Car> carList=new ArrayList<Car>();
        try{
            conn=DBUtil.connection();
            String sql="SELECT * FROM Car WHERE type ='"+type+"'";
            statement= conn.createStatement();
            resultSet=statement.executeQuery(sql);
            while(resultSet.next()){

                if(resultSet.getString("type").equals("大客车")){
                    BigCar bigCar=new BigCar(resultSet.getString("id"),resultSet.getString("license"),resultSet.getString("company"),resultSet.getString("buyTime"),resultSet.getString("type"),resultSet.getInt("mile"),resultSet.getFloat("oilConsumption"),resultSet.getInt("protectMoney"),resultSet.getInt("roadMoney"),resultSet.getFloat("sum"),resultSet.getInt("passengerNum"));
                    carList.add(bigCar);
                }
                if(resultSet.getString("type").equals("小轿车")){
                    SmallCar smallCar=new SmallCar(resultSet.getString("id"),resultSet.getString("license"),resultSet.getString("company"),resultSet.getString("buyTime"),resultSet.getString("type"),resultSet.getInt("mile"),resultSet.getFloat("oilConsumption"),resultSet.getInt("protectMoney"),resultSet.getInt("roadMoney"),resultSet.getFloat("sum"),resultSet.getInt("boxNum"));
                    carList.add(smallCar);
                }
                if(resultSet.getString("type").equals("卡车")){
                    Track track=new Track(resultSet.getString("id"),resultSet.getString("license"),resultSet.getString("company"),resultSet.getString("buyTime"),resultSet.getString("type"),resultSet.getInt("mile"),resultSet.getFloat("oilConsumption"),resultSet.getInt("protectMoney"),resultSet.getInt("roadMoney"),resultSet.getFloat("sum"),resultSet.getInt("Weight"));
                    carList.add(track);
                }



            }
            return carList;
        }catch (Exception e) {
            e.printStackTrace();
            return null;
        }finally {
            if(resultSet!=null){try {
                resultSet.close();}catch (SQLException e){}
            }
            if(statement!=null){try {
                statement.close();}catch (SQLException e){}
            }
            if(conn!=null){try {
                conn.close();}catch (SQLException e){}
            }
        }
    }

    public void modify(Car car){
        Connection conn=null;
        try{
            conn=DBUtil.connection();
            String sql;
            if(car.getType().equals("大客车")){
                sql="UPDATE Car SET license=?,company=?,buyTime=?,mile=?,oilConsumption=?,roadMoney=?,sum=?,passengerNum=? WHERE id='"+car.getId()+"'";
                BigCar bigCar=(BigCar)car;
                PreparedStatement preparedStatement= conn.prepareStatement(sql);
                preparedStatement.setString(1,bigCar.getLicense());
                preparedStatement.setString(2,bigCar.getCompany());
                preparedStatement.setString(3,bigCar.getBuyTime());
                preparedStatement.setInt(4,bigCar.getMile());
                preparedStatement.setFloat(5,bigCar.getOilConsumption());
                preparedStatement.setInt(6,bigCar.getRoadMoney());
                preparedStatement.setFloat(7,bigCar.getSum());
                preparedStatement.setInt(8,bigCar.getPassengerNum());
                preparedStatement.executeUpdate();
            }
            if(car.getType().equals("小轿车")){
                sql="UPDATE Car SET license=?,company=?,buyTime=?,mile=?,oilConsumption=?,roadMoney=?,sum=?,boxNum=? WHERE id='"+car.getId()+"'";
                SmallCar smallCar=(SmallCar) car;
                PreparedStatement preparedStatement= conn.prepareStatement(sql);
                preparedStatement.setString(1,smallCar.getLicense());
                preparedStatement.setString(2,smallCar.getCompany());
                preparedStatement.setString(3,smallCar.getBuyTime());
                preparedStatement.setInt(4,smallCar.getMile());
                preparedStatement.setFloat(5,smallCar.getOilConsumption());
                preparedStatement.setInt(6,smallCar.getRoadMoney());
                preparedStatement.setFloat(7,smallCar.getSum());
                preparedStatement.setInt(8,smallCar.getBoxNum());
                preparedStatement.executeUpdate();
            }
            if(car.getType().equals("卡车")){
                sql="UPDATE Car SET license=?,company=?,buyTime=?,mile=?,oilConsumption=?,roadMoney=?,sum=?,Weight=? WHERE id='"+car.getId()+"'";
                Track track=(Track) car;
                PreparedStatement preparedStatement= conn.prepareStatement(sql);
                preparedStatement.setString(1,track.getLicense());
                preparedStatement.setString(2,track.getCompany());
                preparedStatement.setString(3,track.getBuyTime());
                preparedStatement.setInt(4,track.getMile());
                preparedStatement.setFloat(5,track.getOilConsumption());
                preparedStatement.setInt(6,track.getRoadMoney());
                preparedStatement.setFloat(7,track.getSum());
                preparedStatement.setInt(8,track.getWeight());
                preparedStatement.executeUpdate();
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally {

            if(conn!=null){try {
                conn.close();}catch (SQLException e){}
            }
        }

    }

    public boolean del(String id){
        Connection conn=null;
        Statement st=null;

        try{
            conn=DBUtil.connection();
            String sql ="DELETE FROM Car WHERE id='"+id+"'";
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            preparedStatement.execute();
            return true;
        }
        catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public int judge(){
        int maxsize=5;
        int size=displayall().size();//现有的数量
        if(size==maxsize){
            return 1;
        }
        else if(size==0) return 0;
        else return 2;
    }
    public Statistic statistic(){
        Connection conn=null;
        Statement st=null;
        ResultSet resultSet=null;
        try{
            conn=DBUtil.connection();
            String sql="SELECT * FROM Car";
            Statistic r=new Statistic();
            List<Car> carList=new ArrayList<Car>();
            st=conn.createStatement();
            resultSet=st.executeQuery(sql);
            while(resultSet.next()){
                if(resultSet.getString("type").equals("大客车")){
                    BigCar bigCar=new BigCar(resultSet.getString("id"),resultSet.getString("license"),resultSet.getString("company"),resultSet.getString("buyTime"),resultSet.getString("type"),resultSet.getInt("mile"),resultSet.getFloat("oilConsumption"),resultSet.getInt("protectMoney"),resultSet.getInt("roadMoney"),resultSet.getFloat("sum"),resultSet.getInt("passengerNum"));
                    carList.add(bigCar);
                    r.Bsum++;
                }
                if(resultSet.getString("type").equals("小轿车")){
                    SmallCar smallCar=new SmallCar(resultSet.getString("id"),resultSet.getString("license"),resultSet.getString("company"),resultSet.getString("buyTime"),resultSet.getString("type"),resultSet.getInt("mile"),resultSet.getFloat("oilConsumption"),resultSet.getInt("protectMoney"),resultSet.getInt("roadMoney"),resultSet.getFloat("sum"),resultSet.getInt("boxNum"));
                    carList.add(smallCar);
                    r.Ssum++;
                }
                if(resultSet.getString("type").equals("卡车")){
                    Track track=new Track(resultSet.getString("id"),resultSet.getString("license"),resultSet.getString("company"),resultSet.getString("buyTime"),resultSet.getString("type"),resultSet.getInt("mile"),resultSet.getFloat("oilConsumption"),resultSet.getInt("protectMoney"),resultSet.getInt("roadMoney"),resultSet.getFloat("sum"),resultSet.getInt("Weight"));
                    carList.add(track);
                    r.Tsum++;
                }
            }
            r.carsum=carList.size();

            return r;
        }  catch (Exception e) {
            e.printStackTrace();
            return null;
        }finally {
            if(resultSet!=null){try {
                resultSet.close();}catch (SQLException e){}
            }
            if(st!=null){try {
                st.close();}catch (SQLException e){}
            }
            if(conn!=null){try {
                conn.close();}catch (SQLException e){}
            }
        }

    }
    public boolean searchByid(String id){
        Connection conn=null;
        try {
            conn= DBUtil.connection();
            String sql="SELECT * FROM Car WHERE id='"+id+"'";
            Statement statement =conn.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            if(!resultSet.next()){//如果为空
                return true;
            }else{
                return false;
            }

        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
    }
}
class Statistic {
    public Statistic() {
        super();
        carsum=Bsum=Ssum=Tsum;
    }

    int carsum;
    int Bsum;
    int Ssum;
    int Tsum;

    @Override
    public String toString() {
        return "人员统计如下" +
                "车辆信息库总数：" + carsum +
                "\n 大客车总数：" + Bsum +
                "\n 小轿车总数：" + Ssum +
                "\n 卡车总数：" + Tsum;
    }
}
